﻿use housing 
go
/*函数-得到最低收入家庭数*/
if exists(select * from sysobjects where  name='getZuiDiShouRuZhengCount' and xtype='FN' ) drop function getZuiDiShouRuZhengCount
go
Create function getZuiDiShouRuZhengCount(@yearmonth int,@streetName varchar(16),@docName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if(@familyNum=-1)
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname=@docName and apply_street=@streetName
			end 
		else
			begin
				select @count=Count(*) from Apply  where  apply_docname=@docName and apply_street=@streetName and  EffectDate=@yearmonth
			end 
	end
	else 
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname=@docName and apply_street=@streetName
				and (select Count(*) from Applicant where app_applyid=apply_id )=@familyNum
			end 
		else
			begin
				select @count=Count(*) from Apply  where  apply_docname=@docName and apply_street=@streetName and  EffectDate=@yearmonth
				and (select Count(*) from Applicant where app_applyid=apply_id )=@familyNum
			end		
	end 
	return @count
end 
----SELECT dbo.getZuiDiShouRuZhengCount(200901,'西门','低保证',5,1)

go
/*函数-得到其他家庭数*/
if exists(select * from sysobjects where  name='getDocOterCount' and xtype='FN' ) drop function getDocOterCount
go
Create function getDocOterCount(@yearmonth int,@streetName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if(@familyNum=-1)
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and apply_street=@streetName
			end 
		else
			begin
				select @count=Count(*) from Apply  where   apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and apply_street=@streetName and  EffectDate=@yearmonth
			end 
	end 
	else 
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and 
				(select Count(*) from Applicant where app_applyid=apply_id )=@familyNum and apply_street=@streetName
			end 
		else
			begin
				select @count=Count(*) from Apply  where   apply_docname not in ('低收入家庭','低保证','扶助证','特困证')
				and (select Count(*) from Applicant where app_applyid=apply_id )=@familyNum
				 and apply_street=@streetName and  EffectDate=@yearmonth
			end 

	end 
	return @count
end 
----SELECT dbo.getDocOterCount(200901,'西门',-1,0)

/*函数-得到低收入庭数*/
if exists(select * from sysobjects where  name='getDocOterCount' and xtype='FN' ) drop function getDocOterCount
go
Create function getLowerCount(@yearmonth int,@streetName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if(@familyNum=-1)
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and apply_street=@streetName
			end 
		else
			begin
				select @count=Count(*) from Apply  where   apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and apply_street=@streetName and  EffectDate=@yearmonth
			end 
	end 
	else 
	begin 
		if(@isWholeyear=1)
			begin 
				select @count=Count(*) from Apply  where  apply_docname not in ('低收入家庭','低保证','扶助证','特困证') and 
				(select Count(*) from Applicant where app_applyid=apply_id )=@familyNum and apply_street=@streetName
			end 
		else
			begin
				select @count=Count(*) from Apply  where   apply_docname not in ('低收入家庭','低保证','扶助证','特困证')
				and (select Count(*) from Applicant where app_applyid=apply_id )=@familyNum
				 and apply_street=@streetName and  EffectDate=@yearmonth
			end 

	end 
	return @count
end 
----SELECT dbo.getDocOterCount(200901,'西门',-1,0)
GO
/*函数-得到减免面积*/
if exists(select * from sysobjects where  name='getReduceRecordTotalMoney' and xtype='FN' ) drop function getReduceRecordTotalMoney
go
Create function getReduceRecordTotalMoney(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull(adj_nowreduce,0)) from 
			(
				select distinct  ReduceRecord.adj_noid,ReduceRecord.EffectDate,ReduceRecord.adj_nowreduce
					from ReduceRecord  inner join apply on ReduceRecord.applyid=apply.apply_id  
					where  ReduceRecord.EffectDate=@yearmonth   and  apply_street=@streetName  and adj_nowreduce>0
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))  from ReduceRecord   inner join apply on ReduceRecord.applyid=apply.apply_id  
			where  adj_nowreduce<>0 and  apply_street=@streetName  and ReduceRecord.EffectDate<=@yearmonth  and adj_id in 
(select min(adj_id) from ReduceRecord  group by ReduceRecord.adj_noid,ReduceRecord.EffectDate)
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordTotalMoney(200901,'白云街道')
GO
/*函数-得到减免户数*/
if exists(select * from sysobjects where  name='getReduceRecordCount' and xtype='FN' ) drop function getReduceRecordCount
go
Create function getReduceRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  ReduceRecord.adj_noid,ReduceRecord.EffectDate  from ReduceRecord inner join apply on ReduceRecord.applyid=apply.apply_id  
			where  ReduceRecord.EffectDate=@yearmonth  and  apply_street=@streetName and [adj_nowreduce]>0 
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from ReduceRecord inner join apply on ReduceRecord.applyid=apply.apply_id  
			where   apply_street=@streetName   and ReduceRecord.EffectDate<=@yearmonth
		end	
	return @count
end 
----SELECT dbo.getReduceRecordCount(200901,'西门',0)
GO



/*函数-得到减免户数增加*/
if exists(select * from sysobjects where  name='getReduceRecordAddCount' and xtype='FN' ) drop function getReduceRecordAddCount
go
Create function getReduceRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName and a2.[adj_nowreduce]>0   --上月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowreduce]>0 
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getReduceRecordAddCount(200901,'西门',0)
GO

/*函数-得到减免面积增加*/
if exists(select * from sysobjects where  name='getReduceRecordMoneyAdd' and xtype='FN' ) drop function getReduceRecordMoneyAdd
go
Create function getReduceRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowreduce from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordMoneyAdd(200902,'西门',0)
GO




/*函数-得到减免户数减少*/
if exists(select * from sysobjects where  name='getReduceRecordCountCut' and xtype='FN' ) drop function getReduceRecordCountCut
go
Create function getReduceRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName and  a2.[adj_nowreduce]>0  --本月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1  and  a1.[adj_nowreduce]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getReduceRecordCountCut(200908,'西门',0)
GO

/*函数-得到减免面积减少*/
if exists(select * from sysobjects where  name='getReduceRecordMoneyCut' and xtype='FN' ) drop function getReduceRecordMoneyCut
go
Create function getReduceRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowreduce from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName and a2.adj_nowreduce>0 --本月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.adj_nowreduce>0
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordMoneyCut(200902,'西门',0)
GO






















/*函数-得到实物配租面积*/
if exists(select * from sysobjects where  name='getKindRecordTotalMoney' and xtype='FN' ) drop function getKindRecordTotalMoney
go
Create function getKindRecordTotalMoney(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0)) from 
			(
				select distinct  [KindRecord].adj_noid,[KindRecord].EffectDate,[KindRecord].[adj_nowusearea]
					from [KindRecord]  inner join apply on [KindRecord].applyid=apply.apply_id  
					where  [KindRecord].EffectDate=@yearmonth   and  apply_street=@streetName 
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))  from [KindRecord]   inner join apply on [KindRecord].applyid=apply.apply_id  
			where  adj_nowusearea<>0 and  apply_street=@streetName   and [KindRecord].EffectDate<=@yearmonth and adj_id in 
(select min(adj_id) from [KindRecord]  group by [KindRecord].adj_noid,[KindRecord].EffectDate)
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordTotalMoney('200812','西门',0)
GO

/*函数-得到实物配租户数*/
if exists(select * from sysobjects where  name='getKindRecordCount' and xtype='FN' ) drop function getKindRecordCount
go
Create function getKindRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  [KindRecord].adj_noid,[KindRecord].EffectDate  from [KindRecord] inner join apply on [KindRecord].applyid=apply.apply_id  
			where  [KindRecord].EffectDate=@yearmonth  and  apply_street=@streetName   and [KindRecord].[adj_nowusearea]>0
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from [KindRecord] inner join apply on [KindRecord].applyid=apply.apply_id  
			where   adj_nowusearea<>0 and  apply_street=@streetName  and [KindRecord].[adj_nowusearea]>0   and [KindRecord].EffectDate<=@yearmonth  and adj_id in 
(select min(adj_id) from [KindRecord]  group by [KindRecord].adj_noid,[KindRecord].EffectDate)
		end	
	return @count
end 
----SELECT dbo.getKindRecordCount(200901,'西门街道')
GO


/*函数-得到实物配租户数增加*/
if exists(select * from sysobjects where  name='getKindRecordAddCount' and xtype='FN' ) drop function getKindRecordAddCount
go
Create function getKindRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  and a2.[adj_nowusearea]>0 --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowusearea]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getKindRecordAddCount(200901,'西门',0)
GO

/*函数-得到实物配租面积增加*/
if exists(select * from sysobjects where  name='getKindRecordMoneyAdd' and xtype='FN' ) drop function getKindRecordMoneyAdd
go
Create function getKindRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyAdd(200902,'西门',0)
GO




/*函数-得到实物配租户数减少*/
if exists(select * from sysobjects where  name='getKindRecordCountCut' and xtype='FN' ) drop function getKindRecordCountCut
go
Create function getKindRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName   and a2.[adj_nowusearea]>0--本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.[adj_nowusearea]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getKindRecordCountCut(200908,'西门',0)
GO

/*函数-得到实物配租面积减少*/
if exists(select * from sysobjects where  name='getKindRecordMoneyCut' and xtype='FN' ) drop function getKindRecordMoneyCut
go
Create function getKindRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName  --本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyCut(200902,'西门',0)
GO


/*函数-本月实物配租补发户数*/
if exists(select * from sysobjects where  name='getAddSubsidyNumCount' and xtype='FN' ) drop function getAddSubsidyNumCount
go
Create function getAddSubsidyNumCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  EffectDate,apply_noid  from apply
			where  EffectDate=@yearmonth  and  apply_street=@streetName  and isnull(AddSubsidyNum,0)>0
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from apply
			where    apply_street=@streetName  and isnull(AddSubsidyNum,0)>0
		end	
	return @count
end 
----SELECT dbo.getAddSubsidyNumCount(200901,'西门',0)
GO

/*函数-本月补贴补发金额*/
if exists(select * from sysobjects where  name='getAddSubsidyNumSum' and xtype='FN' ) drop function getAddSubsidyNumSum
go
Create function getAddSubsidyNumSum(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Sum(AddSubsidyNum) 
			from 
			(
				select isnull(AddSubsidyNum,0) as AddSubsidyNum from apply
			where  EffectDate=@yearmonth  and  apply_street=@streetName  and isnull(AddSubsidyNum,0)>0
			) as mytable
		end 
	else
		begin 
			select @count=Sum(isnull(AddSubsidyNum,0)) from apply
			where    apply_street=@streetName  and isnull(AddSubsidyNum,0)>0
		end	
	return @count
end 
----SELECT dbo.getAddSubsidyNumSum(200901,'西门',0)
GO





/*函数-街道名字得到街道编号*/
if exists(select * from sysobjects where  name='getStreetNumIdByStreetName' and xtype='FN' ) drop function getStreetNumIdByStreetName
go
Create function getStreetNumIdByStreetName(@streetName varchar(16))
returns varchar(3)
as 
begin 
	declare @noid varchar(3)
	 select @noid=[str_noid] from [StreetNumId] where [str_streetname]=@streetName
	return  @noid
end 
----SELECT dbo.getStreetNumIdByStreetName('西门街道')
GO




GO
/*函数-得到实物配租户数*/
if exists(select * from sysobjects where  name='getKindRecordCount' and xtype='FN' ) drop function getKindRecordCount
go
Create function getKindRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  KindRecord.adj_noid,KindRecord.EffectDate  from KindRecord inner join apply on KindRecord.applyid=apply.apply_id  
			where  KindRecord.EffectDate=@yearmonth  and  apply_street=@streetName  
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from KindRecord inner join apply on KindRecord.applyid=apply.apply_id  
			where   apply_street=@streetName   and KindRecord.EffectDate<=@yearmonth
		end	
	return @count
end 
----SELECT dbo.getKindRecordCount(200901,'西门',0)
GO

GO
/*函数-得到实物配租面积*/
if exists(select * from sysobjects where  name='getKindRecordAreaSum' and xtype='FN' ) drop function getKindRecordAreaSum
go
Create function getKindRecordAreaSum(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0)) from 
			(
				select distinct  [KindRecord].adj_noid,[KindRecord].EffectDate,[KindRecord].[adj_nowusearea]
					from [KindRecord]  inner join apply on [KindRecord].applyid=apply.apply_id  
					where  [KindRecord].EffectDate=@yearmonth   and  apply_street=@streetName  and [KindRecord].[adj_nowusearea]>0
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))  from [KindRecord]   inner join apply on [KindRecord].applyid=apply.apply_id  
			where  [adj_nowusearea]<>0 and  apply_street=@streetName  and [KindRecord].EffectDate<=@yearmonth  and adj_id in 
(select min(adj_id) from [KindRecord]  group by [KindRecord].adj_noid,[KindRecord].EffectDate)
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordAreaSum(200901,'西门',0)
GO





/*函数-得到实物配租户数增加*/
if exists(select * from sysobjects where  name='getKindRecordAddCount' and xtype='FN' ) drop function getKindRecordAddCount
go
Create function getKindRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  and a2.[adj_nowusearea]>0 --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowusearea]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getKindRecordAddCount(200901,'西门',0)
GO

/*函数-得到实物配租面积增加*/
if exists(select * from sysobjects where  name='getKindRecordMoneyAdd' and xtype='FN' ) drop function getKindRecordMoneyAdd
go
Create function getKindRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyAdd(200902,'西门',0)
GO




/*函数-得到实物配租户数减少*/
if exists(select * from sysobjects where  name='getKindRecordCountCut' and xtype='FN' ) drop function getKindRecordCountCut
go
Create function getKindRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName   and a2.[adj_nowusearea]>0--本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.[adj_nowusearea]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getKindRecordCountCut(200908,'西门',0)
GO

/*函数-得到实物配租面积减少*/
if exists(select * from sysobjects where  name='getKindRecordMoneyCut' and xtype='FN' ) drop function getKindRecordMoneyCut
go
Create function getKindRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @TotalMoney int
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName  --本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyCut(200902,'西门',0)




















